package cn.pzhuweb.dao.imp;

import cn.pzhuweb.dao.CarDAO;
import cn.pzhuweb.pojo.Car;
import cn.pzhuweb.util.C3P0Util;
import cn.pzhuweb.util.DBUtil;
import cn.pzhuweb.util.DruidUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Yancy xiao
 * @create 2022-11-15 10:31
 */
public class CarDAOImp implements CarDAO {
    private JdbcTemplate template = new JdbcTemplate(DruidUtil.getDs());

    @Override
    public List<Car> selectByKeyWords(String keyword) {
        String sql = "select * from car where car_name like \"%" + keyword + "%\"" + " or car_type like \"%" + keyword + "%\"";
        return template.query(sql, new BeanPropertyRowMapper<Car>(Car.class));
/*        Connection con = DBUtil.getConnection();
        PreparedStatement psta = null;
        try {
            String sql = "select * from car where car_name like \"%" + keyword + "%\"" + " or car_type like \"%" + keyword + "%\"";
            psta = con.prepareStatement(sql);
            //psta.setObject(2, keyword);
            ResultSet resultSet = psta.executeQuery();
            return resultSet;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(psta, con);
        }
        return null;*/
    }

    @Override
    public int insert(Car car) {
        Connection con = C3P0Util.getConnection();
        PreparedStatement psta = null;
        String sql = "insert into car values(?,?,?,?)";
        try {
            psta = con.prepareStatement(sql);
            psta.setObject(1, car.getCar_id());
            psta.setObject(2, car.getCar_name());
            psta.setObject(3, car.getCar_type());
            psta.setObject(4, car.getCar_price());
            return psta.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(psta, con);
        }
        return 0;
    }

    @Override
    public int delete(Integer id) {
        Connection con = C3P0Util.getConnection();
        PreparedStatement psta = null;
        String sql = "DELETE FROM car WHERE car_id = ?";
        try {
            psta = con.prepareStatement(sql);
            psta.setObject(1, id);
            return psta.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(psta, con);
        }
        return 0;
    }

    @Override
    public int update(Car car) {
        Connection con = C3P0Util.getConnection();
        PreparedStatement psta = null;
        String sql = "update car set car_id=?,car_name=?,car_type=?,car_price=? where car_id=?";
        try {
            psta = con.prepareStatement(sql);
            psta.setObject(1, car.getCar_id());
            psta.setObject(2, car.getCar_name());
            psta.setObject(3, car.getCar_type());
            psta.setObject(4, car.getCar_price());
            psta.setObject(5, car.getCar_id());
            return psta.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(psta, con);
        }
        return 0;
    }

    @Override
    public Car selectById(Integer id) {
        //return template.queryForObject(sql, new BeanPropertyRowMapper<Car>(Car.class), id);
        Connection con = C3P0Util.getConnection();
        PreparedStatement psta = null;
        ResultSet res = null;
        String sql = "select * from car where car_id = ?";
        try {
            psta = con.prepareStatement(sql);
            psta.setInt(1, id);
            res = psta.executeQuery();
            if (res.next()) {
                int cid = res.getInt(1);
                String cname = res.getString(2);
                String ctype = res.getString(3);
                Double cprice = res.getDouble(4);
                return new Car(cid, cname, ctype, cprice);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(psta, con);
        }
        return null;
    }

    @Override
    public List<Car> selectAll() {
        String sql = "select SQL_NO_CACHE * from car";
        return template.query(sql, new BeanPropertyRowMapper<Car>(Car.class));
    }
}